<?php
namespace Core;

class Sql
{

    private $master = '';

    private $field = '*';

    private $countField = 'count(*) as total';

    private $table = '';

    private $leftJoin = [];

    private $groupBy = [];

    private $insertOrUpdate = [];

    private $where = [];

    private $order = [];

    private $page = 1;

    private $pageSize = 1000000;

    private $limit = '';

    private $having = '';

    private $placeholder = [];

    private $total = [];

    //恢复默认设置
    private function reset()
    {
        $this->master = '';
        $this->field = '*';
        $this->countField = 'count(*) as total';
        $this->table = '';
        $this->leftJoin = [];
        $this->groupBy = [];
        $this->insertOrUpdate = [];
        $this->where = [];
        $this->order = [];
        $this->page = 1;
        $this->pageSize = 10;
        $this->limit = '';
        $this->having = '';
        $this->placeholder = [];
        /*
            不能重置$this->total
        */
    }

    public function startWith($string, $test)
    {
        $result = strpos($string, $test);
        return ($result !== false && $result == 0);
    }

    //设置master
    public function setMaster()
    {
        $this->master = '/*FORCE_MASTER*/';
        return $this;
    }

    //设置字段
    public function field($field)
    {
        $this->field = $field;
        return $this;
    }

    //设置字段
    public function countField($field)
    {
        $this->countField = $field;
        return $this;
    }

    //设置表
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }

    //左联结
    public function leftJoin($table1, $alias, $field1, $operator, $table2, $field2)
    {
        $this->leftJoin[] = "left join {$table1} as {$alias} on {$alias}.{$field1} {$operator} {$table2}.{$field2}";
        return $this;
    }

    //右联结
    public function rightJoin($table1, $alias, $field1, $operator, $table2, $field2)
    {
        $this->leftJoin[] = "right join {$table1} as {$alias} on {$alias}.{$field1} {$operator} {$table2}.{$field2}";
        return $this;
    }

    //左联结
    public function getLeftJoin()
    {
        if(empty($this->leftJoin)){
            return '';
        }
        else{
            return implode(' ', $this->leftJoin);
        }
    }

    //group by
    public function groupBy($table, $field)
    {
        if($table == ''){
            $temp = $field;
        }
        else{
            $temp = "{$table}.{$field}";
        }
        $this->groupBy[] = $temp;
        return $this;
    }

    //group by
    public function getGroupBy()
    {
        if(empty($this->groupBy)){
            return '';
        }
        else{
            return 'group by ' . implode(',', $this->groupBy);
        }
    }

    public function set($field, $value)
    {
        if($this->startWith($field, '&')){
            $field = substr($field, 1);
            $this->insertOrUpdate['field'][] = $field;
            $this->insertOrUpdate['value'][] = $value;
        }
        else{
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->insertOrUpdate['field'][] = $field;
            $this->insertOrUpdate['value'][] = $placeholder;
        }
        return $this;
    }

    private function getUniqid($field)
    {
        $result = uniqid(":{$field}_", true);
        $result = str_replace('.', '', $result);
        return $result;
    }

    //设置查询条件
    public function where($field, $operator, $value)
    {
        if(in_array($operator, ['<', '<=', '=', '>=', '>', '<>', '!='])){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} {$operator} {$placeholder}";
        }
        else if(in_array($operator, ['like', 'not like'])){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} {$operator} {$placeholder}";
        }
        else if($operator == 'between and'){
            $placeholder0 = $this->getUniqid($field);
            $placeholder1 = $this->getUniqid($field);
            $this->placeholder[$placeholder0] = $value[0];
            $this->placeholder[$placeholder1] = $value[1];
            $this->where[] = "{$field} between {$placeholder0} and {$placeholder1}";
        }
        else if($operator == 'not between and'){
            $placeholder0 = $this->getUniqid($field);
            $placeholder1 = $this->getUniqid($field);
            $this->placeholder[$placeholder0] = $value[0];
            $this->placeholder[$placeholder1] = $value[1];
            $this->where[] = "{$field} not between {$placeholder0} and {$placeholder1}";
        }
        else if($operator == 'contain'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "FIND_IN_SET({$placeholder},{$field})>0";
        }
        else if($operator == 'not contain'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "FIND_IN_SET({$placeholder},{$field})<=0";
        }
        else if($operator == 'match word'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} REGEXP '[[:<:]]{$placeholder}[[:>:]]'";
        }
        else if(in_array($operator, ['in', 'not in'])){
            $list = explode(',', $value);
            $count = count($list);
            $placeholder = $this->getUniqid($field);
            $placeholderAll = [];
            for($i = 0;$i < $count;$i++){
                $temp = "{$placeholder}_{$i}";
                $placeholderAll[] = $temp;
                $this->placeholder[$temp] = $list[$i];
            }
            $placeholderAll = implode(',', $placeholderAll);
            $this->where[] = "{$field} {$operator} ({$placeholderAll})";
        }
        return $this;
    }

    public function whereRaw($string)
    {
        $this->where[] = $string;
    }

    //设置查询条件and
    public function andWhere($field, $operator, $value)
    {
        $this->whereExpression('and');
        $this->where($field, $operator, $value);
        return $this;
    }

    //设置查询条件or
    public function orWhere($field, $operator, $value)
    {
        $this->whereExpression('or');
        $this->where($field, $operator, $value);
        return $this;
    }

    //设置筛选连接符
    public function whereExpression($operator)
    {
        switch($operator){
            case '(':
                $this->where[] = '(';
                break;
            case ')':
                $this->where[] = ')';
                break;
            case 'and':
                $this->where[] = ' and ';
                break;
            case 'or':
                $this->where[] = ' or ';
                break;
        }
        return $this;
    }

    //设置筛选连接符
    public function whereExpressions($operators = [])
    {
        foreach($operators as $operator){
            $this->whereExpression($operator);
        }
        return $this;
    }

    //设置排序
    public function setOrder($field, $method)
    {
        $this->order[] = "{$field} {$method}";
        return $this;
    }

    //清除排序
    public function clearOrder()
    {
        $this->order = [];
        return $this;
    }

    //设置排序
    public function setOrderRaw($data)
    {
        $this->order[] = $data;
        return $this;
    }

    //设置排序
    public function setOrderRand()
    {
        $this->order[] = 'rand()';
        return $this;
    }

    //设置页码
    public function setPage($page = 1)
    {
        $this->page = $page;
        return $this;
    }

    //获取页码
    public function getPage()
    {
        return $this->page;
    }

    //设置每一页多少条数据
    public function setPageSize($pageSize = 10)
    {
        $this->pageSize = $pageSize;
        return $this;
    }

    public function getPageSize()
    {
        return $this->pageSize;
    }

    //设置限制数
    public function setLimit()
    {
        $page = $this->getPage();
        $pageSize = $this->getPageSize();
        $start = ($page - 1) * $pageSize;
        $this->limit = "limit {$start},{$pageSize}";
        return $this;
    }

    public function having($having = '')
    {
        $this->having = 'having ' . $having;
    }

    //返回where
    public function getWhere()
    {
        $where = '';
        if(!empty($this->where)){
            $where = 'where ' . implode('', $this->where);
        }
        return $where;
    }

    //返回order
    public function getOrder()
    {
        $order = '';
        if(!empty($this->order)){
            $order = 'order by ' . implode(',', $this->order);
        }
        return $order;
    }

    //返回sql语句
    public function get()
    {
        $where = $this->getWhere();
        $order = $this->getOrder();
        $leftJoin = $this->getLeftJoin();
        $groupBy = $this->getGroupBy();
        $result = [
            'sql' => "{$this->master}select {$this->field} from {$this->table} {$leftJoin} {$where} {$groupBy} {$this->having} {$order} {$this->limit}",
            'placeholder' => $this->placeholder
        ];
        $this->total = [
            'sql' => "{$this->master}select {$this->countField} from {$this->table} {$leftJoin} {$where} {$this->having}",
            'placeholder' => $this->placeholder
        ];
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getTotalSql()
    {
        $this->reset();
        return $this->total;
    }

    //返回sql语句
    public function getInsertSql()
    {
        $field = implode(',', $this->insertOrUpdate['field']);
        $value = implode(',', $this->insertOrUpdate['value']);
        $result = [
            'sql' => "insert into {$this->table}({$field}) values({$value})",
            'placeholder' => $this->placeholder
        ];
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getUpdateSql()
    {
        $where = $this->getWhere();
        $update = [];
        foreach($this->insertOrUpdate['field'] as $k => $field){
            $update[] = "{$field}={$this->insertOrUpdate['value'][$k]}";
        }
        $update = implode(',', $update);
        $result = [
            'sql' => "update {$this->table} set {$update} {$where}",
            'placeholder' => $this->placeholder
        ];
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getDeleteSql()
    {
        $where = $this->getWhere();
        $result = [
            'sql' => "delete from {$this->table} {$where}",
            'placeholder' => $this->placeholder
        ];
        $this->reset();
        return $result;
    }

}